package com.common.doc.xsl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;

import com.common.report.vo.ReportTableViewVO;
import com.common.report.vo.ReportTitleVO;
import com.common.util.StringUtil;



/**
 * @author YunDa11
 * 
 */
public class POIFManager
{
	private static Logger  log = Logger.getLogger(POIFManager.class);
	private static POIFManager _cell;

	private POIFSFileSystem _poif;

	private HSSFWorkbook _hssfwb;

	private static HSSFCellStyle style;
	
	private static HSSFFont font;
	
	private HSSFSheet _sheet;
	public static void setFont(HSSFFont font) {
		POIFManager.font = font;
	}

	public HSSFCellStyle getStyle(HSSFWorkbook wb) {
		 HSSFCellStyle cellStyle = wb.createCellStyle();
         cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
         cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
         cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
         cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		 return cellStyle;
	}

	@SuppressWarnings("static-access")
	public void setStyle(HSSFCellStyle style) {
		this.style = style;
	}
	public POIFManager()
	{
		super();
		// TODO Auto-generated constructor stub
		_hssfwb = new HSSFWorkbook();
		
		this.style = _hssfwb.createCellStyle();
		this.style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		this.style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		this.style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		this.style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		this.style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		this.style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		this.style.setWrapText(true); 
		this.font = _hssfwb.createFont();
		this.font.setFontName("宋体");
		this.font.setFontHeightInPoints((short)11);
		this.style.setFont(this.font);
		this._sheet = _hssfwb.createSheet();
		this._sheet.setDefaultColumnWidth(12);
		this._sheet.setDefaultRowHeight((short) 15);
	}
	@SuppressWarnings("static-access")
	public POIFManager(String fileName)
	{
		super();
		// TODO Auto-generated constructor stub
		try
		{
			_poif = new POIFSFileSystem(new FileInputStream(fileName));
			_hssfwb = new HSSFWorkbook(_poif);
			this.style = _hssfwb.createCellStyle();
			this.style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			this.style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			this.style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			this.style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			this.style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			this.style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			this.style.setWrapText(true); 
			this.font = _hssfwb.createFont();
			this.font.setFontName("宋体");
			this.font.setFontHeightInPoints((short)11);
			this.style.setFont(this.font);
		} catch (FileNotFoundException e)
		{
			// TODO Auto-generated catch block
			log.error(e.getMessage(),e);
		} catch (IOException e)
		{
			// TODO Auto-generated catch block
			log.error(e.getMessage(),e);
		}
	}
	public static HSSFCell createTitle(HSSFSheet sheet, int rowIndex,
			int colIndex)
	{
		HSSFRow row = sheet.getRow(rowIndex);
		if(row==null){
			row = sheet.createRow(rowIndex);
		}
		HSSFCell cell = row.getCell((short) colIndex);
		if (cell == null)
			cell = row.createCell((short) colIndex);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		style.setFont(new Font());
		cell.setCellStyle(style);
		
		return cell;
	}
	public static HSSFCell createCell(HSSFSheet sheet, int rowIndex,
			int colIndex)
	{
		HSSFRow row = sheet.getRow(rowIndex);
		if(row==null){
			row = sheet.createRow(rowIndex);
		}
		HSSFCell cell = row.getCell((short) colIndex);
		if (cell == null)
			cell = row.createCell((short) colIndex);
		cell.setCellStyle(style);
		
		return cell;
	}
	
	public static HSSFCell createCell(HSSFSheet sheet, int rowIndex,
			int colIndex,String create)
	{
		HSSFRow row = sheet.createRow(rowIndex);
		/*if(row==null){
			row = sheet.createRow(rowIndex);
		}*/
		HSSFCell cell = row.getCell((short) colIndex);
		if (cell == null)
			cell = row.createCell((short) colIndex);
		cell.setCellStyle(style);
		
		return cell;
	}
	
	public HSSFSheet createSheet()
	{
		return _hssfwb.createSheet();
	}

	public HSSFSheet createSheet(String string)
	{
		return _hssfwb.createSheet(string);
	}

	public HSSFSheet cloneSheet(int i)
	{
		return _hssfwb.cloneSheet(i);
	}

	public HSSFSheet getSheet(int i)
	{
		return _hssfwb.getSheetAt(i);
	}

	public static POIFManager getCell4T(String fileName)
	{
		_cell = new POIFManager(fileName);
		return _cell;
	}

	public static POIFManager createCell4T(){
		_cell = new POIFManager();
		
		return _cell;
	}
	
	
	public void iteratorValue4XLS(java.util.List dataList, HSSFSheet sheet,
			int row, ICellCommander _ccmd)
	{
		for (int i = 0; dataList != null && i < dataList.size(); i++)
		{
			Object obj = (Object) dataList.get(i);
			short currRow = (short) (row + i);
			if (_ccmd != null)
			{
				_ccmd.doInIterator(obj, sheet, currRow, i);
			}
		}
	}
	
	public void iteratorValue4XLS(ReportTableViewVO vo)
	{
		if(vo!=null){
			int rowStart = 2;
			int colStart = 2;
			_sheet.addMergedRegion(new   Region(rowStart-1,(short)colStart,1,(short)(vo.getTitleList().size()+colStart)));
			HSSFCell _cell = POIFManager.createTitle(this._sheet, rowStart-1, colStart);
			CellStyle style = _hssfwb.createCellStyle();
			style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
			style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
			style.setBorderRight(HSSFCellStyle.BORDER_NONE);
			style.setBorderTop(HSSFCellStyle.BORDER_NONE);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			_cell.setCellStyle(style);

			_cell.setCellValue(vo.getReportName());
			
			_cell = POIFManager.createTitle(this._sheet, rowStart, colStart);
			_cell.setCellValue(vo.getMentName());
			for (int i = 0; vo.getTitleList()!=null && i < vo.getTitleList().size(); i++) {
				ReportTitleVO titles = vo.getTitleList().get(i);
				_cell = POIFManager.createTitle(this._sheet, rowStart, i+colStart+1);
				_cell.setCellValue(titles.getName());
//				System.out.println("title.getName()=="+title.getName());
				for (int j = 0; vo.getMentList()!=null && j < vo.getMentList().size(); j++)
				{
					ReportTitleVO ments = vo.getMentList().get(j);
					if(i==0){
						_cell = POIFManager.createCell(this._sheet, j+rowStart+1, colStart);
						_cell.setCellValue(ments.getName());
						
					}
					_cell = POIFManager.createCell(this._sheet, j+rowStart+1, i+colStart+1);
					Object counts = vo.getDataMap().get(titles.getCode()+"_"+ments.getCode());
					if(StringUtil.isBlank(counts)){
						counts="0";
					}
					_cell.setCellValue(String.valueOf(counts));
					
					//System.out.println("key===="+titles.getCode()+"_"+ments.getCode());
				}
			}
		}
	}
	
	public void iteratorMutilValue4XLS(ReportTableViewVO vo){
		if(vo!=null){
			int rowStart = 2;
			int colStart = 2;
			_sheet.addMergedRegion(new   Region(rowStart-1,(short)colStart,1,(short)(vo.getTitleList().size()*2+colStart)));//标题区域
			HSSFCell _cell = POIFManager.createTitle(this._sheet, rowStart-1, colStart);
			CellStyle style = _hssfwb.createCellStyle();
			style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
			style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
			style.setBorderRight(HSSFCellStyle.BORDER_NONE);
			style.setBorderTop(HSSFCellStyle.BORDER_NONE);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			_cell.setCellStyle(style);

			_cell.setCellValue(vo.getReportName());
			
			_sheet.addMergedRegion(new   Region(rowStart,(short)colStart,rowStart+1,(short) (colStart)));//区域名称区域
			
			
			_cell = POIFManager.createTitle(this._sheet, rowStart, colStart);
			_cell.setCellValue(vo.getMentName());
			_cell = POIFManager.createTitle(this._sheet, rowStart+1, colStart);
			
			
			for (int i = 0; vo.getTitleList()!=null && i < vo.getTitleList().size(); i++) {
				ReportTitleVO titles = vo.getTitleList().get(i);
				int colNum = titles.getTitleList().size();
				
				//第一行标题区域
				short colstart = (short) (colStart+i*colNum+1);
				short colto = (short) (colStart+i*colNum+colNum);
				//log.info("colstart======"+colstart+"==========colto========"+colto);
				_sheet.addMergedRegion(new Region(rowStart,(short)colstart,(rowStart),(short) colto));
				
				_cell = POIFManager.createTitle(this._sheet, rowStart, colstart);
				_cell.setCellValue(titles.getName());
				for(int k = 0;k<titles.getTitleList().size();k++){
					ReportTitleVO t = titles.getTitleList().get(k);
					_cell = POIFManager.createTitle(this._sheet, rowStart, i*colNum+colStart+1+k);
					_cell = POIFManager.createTitle(this._sheet, rowStart+1, i*colNum+colStart+1+k);
					_cell.setCellValue(t.getName());
					
					
					for (int j = 0; vo.getMentList()!=null && j < vo.getMentList().size(); j++){
						ReportTitleVO ments = vo.getMentList().get(j);
						if(i==0){
							_cell = POIFManager.createCell(this._sheet, j+rowStart+2, colStart);
							_cell.setCellValue(ments.getName());
							
						}
						_cell = POIFManager.createCell(this._sheet, j+rowStart+2, colStart+1+i*colNum+k);
						String key = t.getCode()+"-"+titles.getCode()+"_"+ments.getCode();
						log.info("key=============="+key);
						Object counts = vo.getDataMap().get(key);
						if(StringUtil.isBlank(counts)){
							counts="0";
						}
						_cell.setCellValue(String.valueOf(counts));
						
					
					}
				}
				
//				System.out.println("title.getName()=="+title.getName());
			
			}
		}
	}
	

	
	public void writ2disk(String fileName)
	{
		FileOutputStream _out;
		try
		{
			_out = new FileOutputStream(fileName);
			this._hssfwb.write(_out);
			_out.close();
		} catch (FileNotFoundException e)
		{
			log.error(e.getMessage(),e);
		} catch (IOException e)
		{
			e.printStackTrace();
		}
	}
	
	public void writ2OutStream(OutputStream out)
	{
		try {
			this._hssfwb.write(out);
		} catch (IOException e) {
			log.error(e.getMessage(),e);
		}
		
	}
/*	public void iteratorValue4XLS(ReportTableViewVO vo)
	{
		if(vo!=null){
			HSSFCell _cell = POIFManager.createTitle(this._sheet, 1, 0);
			_cell.setCellValue("序号");
			for (int i = 0; vo.getTitleList()!=null && i < vo.getTitleList().size(); i++) {
				ReportTitleVO title = vo.getTitleList().get(i);
				_cell = POIFManager.createTitle(this._sheet, 1, i+1);
				_cell.setCellValue(title.getName());
//				System.out.println("title.getName()=="+title.getName());
				for (int j = 0; vo.getDataList()!=null && j < vo.getDataList().size(); j++)
				{
					if(i==0){
						_cell = POIFManager.createCell(this._sheet, j+2, 0);
						_cell.setCellValue(j+1);
						
					}
					Hashtable table = vo.getDataList().get(j);
					_cell = POIFManager.createCell(this._sheet, j+2, i+1);
					_cell.setCellValue(String.valueOf(table.get(title.getCode())));
//					System.out.println("table.get(title.getCode())=="+table.get(title.getCode()));
				}
			}
		}
	}*/
	public HSSFWorkbook get_hssfwb() {
		
		return _hssfwb;
	}

	public void set_hssfwb(HSSFWorkbook _hssfwb) {
		this._hssfwb = _hssfwb;
	}
}
